import logging
import operator
import openpyxl
import datetime
import os
import sys
import configparser
import time
from hanchlogger import logger
import hanchxl
import ChengBanBuMen
import docx
from docx.shared import RGBColor

timeCur = datetime.datetime.now()
timeCurStr = '{}-{}-{} {}:{}:{}'.format(timeCur.year, timeCur.month, timeCur.day, timeCur.hour, timeCur.minute, timeCur.second)
dirPath = 'E:\\日常工作\\数据组日常工作-韩冲\\生成周报'
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
dirPathShare = '\\\\172.16.10.175\\共享文件\\数据组\\周报数据'

logFile = dirPath + '\\WeeklyReport.log'
logger.get_log(logFile, 'LoggerWeeklyReport', logging.DEBUG)

iniFilePathWeekly = dirPath + '\\WeeklyReport.ini'
iniConfigWeekly = configparser.ConfigParser(allow_no_value=True)
iniConfigWeekly.read(iniFilePathWeekly, encoding='utf-8')


def get_config_value(ini_config, section, section_key, value_default):
    sectionValue = value_default
    if ini_config.has_section(section):
        if ini_config.has_option(section, section_key):
            sectionValue = ini_config.get(section, section_key, )
            if sectionValue is not None and sectionValue != '':
                sectionValue = '{}'.format(sectionValue)
    return sectionValue


# 是否自定义工作目录
customizeWorkFolder = get_config_value(iniConfigWeekly, 'global', 'isCustomizeWorkFolder', '0')
if customizeWorkFolder != '0':
    workFolder = get_config_value(iniConfigWeekly, 'global', 'workFolder', dirPath)
    if workFolder != '' and dirPath != workFolder:
        if os.path.exists(workFolder):
            dirPath = workFolder
            logger.debug('是否自定义工作目录为：{}'.format(dirPath))
        else:
            logger.debug('自定义的工作目录不存在，工作目录：{}'.format(dirPath))
    else:
        logger.debug('工作目录未填写或者为空，或者与默认值相同，使用默认值：{}'.format(dirPath))
else:
    logger.debug('不使用自定义的工作目录，使用默认值:{}'.format(dirPath))

OrderCountFolder = get_config_value(iniConfigWeekly, 'global', 'OrderCountFolder', '')
if OrderCountFolder is not None and OrderCountFolder != '':
    dirPathShare = dirPath + OrderCountFolder
logger.debug('汇总文件目录：{}'.format(dirPathShare))

fileCountZhouBao = dirPathShare + '\\周报汇总数据信息.xlsx'
fileJieDan = dirPath + '\\全部工单.xlsx'
fileJieDanSave = dirPath + '\\全部工单-修改承办部门.xlsx'
fileZhouBaoMuBan = dirPath + '\\模板-[内部]拱辰街道接诉即办工作情况周报.docx'
fileZhouBaoSave = dirPath + '\\[内部] 拱辰街道接诉即办工作情况周报.docx'

# 打开周报模板文件
docMuBan = docx.Document(fileZhouBaoMuBan)

bookCountZhouBao = None
sheetNamesList = ['日诉求量', '分类统计', '科室接单量', '社区村接单量', '总体诉求情况']
sheetListOfJieDan = {}
if os.path.exists(fileCountZhouBao):
    bookCountZhouBao = openpyxl.load_workbook(fileCountZhouBao, data_only=True)
    sheetNames = bookCountZhouBao.sheetnames
    for sheetName in sheetNamesList:
        if sheetName in sheetNames:
            sheetListOfJieDan[sheetName] = bookCountZhouBao[sheetName]
        else:
            sheetNew = bookCountZhouBao.create_sheet(title=sheetName, index=1)
            sheetNew.cell(1, 1).value = '时间戳'
            if sheetName == '日诉求量':
                sheetNew.cell(1, 2).value = '日期'
                sheetNew.cell(1, 3).value = '日诉求量'
                sheetNew.cell(1, 4).value = '区派'
                sheetNew.cell(1, 5).value = '市派'
            elif sheetName == '总体诉求情况':
                sheetNew.cell(1, 2).value = '日期'
                sheetNew.cell(1, 2).value = '序号'
                sheetNew.cell(1, 3).value = '周接单量'
                sheetNew.cell(1, 4).value = '区派'
                sheetNew.cell(1, 5).value = '市派'
                sheetNew.cell(1, 6).value = '开始日期'
                sheetNew.cell(1, 7).value = '结束日期'
            elif sheetName == '科室接单量' or sheetName == '社区村接单量' or sheetName == '分类统计':
                sheetNew.cell(1, 2).value = '接单总量'
            sheetListOfJieDan[sheetName] = bookCountZhouBao[sheetName]
else:
    bookCountZhouBao = openpyxl.Workbook()  # 创建一个excel文件
    sheetCur = bookCountZhouBao.active  # 获得一个的工作表
    for sheetName in sheetNamesList:
        sheetNew = bookCountZhouBao.create_sheet(title=sheetName, index=1)
        sheetNew.cell(1, 1).value = '时间戳'
        if sheetName == '日诉求量':
            sheetNew.cell(1, 2).value = '日期'
            sheetNew.cell(1, 3).value = '日诉求量'
            sheetNew.cell(1, 4).value = '区派'
            sheetNew.cell(1, 5).value = '市派'
        elif sheetName == '总体诉求情况':
            sheetNew.cell(1, 2).value = '日期'
            sheetNew.cell(1, 2).value = '序号'
            sheetNew.cell(1, 3).value = '周接单量'
            sheetNew.cell(1, 4).value = '区派'
            sheetNew.cell(1, 5).value = '市派'
            sheetNew.cell(1, 6).value = '开始日期'
            sheetNew.cell(1, 7).value = '结束日期'
        elif sheetName == '科室接单量' or sheetName == '社区村接单量' or sheetName == '分类统计':
            sheetNew.cell(1, 2).value = '接单总量'
        sheetListOfJieDan[sheetName] = bookCountZhouBao[sheetName]
    bookCountZhouBao.remove(sheetCur)
bookCountZhouBao.save(fileCountZhouBao)

bookJieDan = openpyxl.load_workbook(fileJieDan)
sheetJieDan = bookJieDan.active
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))


class dateJianGeInFo(object):
    def __init__(self, date='', start=0, end=0, count=0, time_d=datetime.datetime.now(), shi_pai=0, qu_pai=0):
        self.date = date
        self.start = start
        self.end = end
        self.count = count
        self.time_d = time_d
        self.shiPai = shi_pai
        self.quPai = qu_pai


# 统计日诉求量 开始
dayCountList = {}
for dayJianGe in range(10, 0, -1):
    jianGeDate = dateJianGeInFo()
    timeSearchDay = datetime.datetime.fromtimestamp(timeCur.timestamp() - 60 * 60 * 24 * dayJianGe).strftime('%Y-%m-%d')
    timeSearchDayStartStr = timeSearchDay + '  00:00:00'
    timeSearchDayEndStr = timeSearchDay + '  23:59:59'
    timeSearchDayStart = time.strptime(timeSearchDayStartStr, '%Y-%m-%d %H:%M:%S')
    timeSearchDayEnd = time.strptime(timeSearchDayEndStr, '%Y-%m-%d %H:%M:%S')
    jianGeDate.start = int(time.mktime(timeSearchDayStart))
    jianGeDate.end = int(time.mktime(timeSearchDayEnd))
    jianGeDate.time_d = datetime.datetime.fromtimestamp(jianGeDate.start)
    jianGeDate.date = '{}.{}'.format(timeSearchDayStart.tm_mon, timeSearchDayStart.tm_mday)
    jianGeDate.count = 0
    logger.debug('{} 间隔 {} 天'.format(jianGeDate.date, dayJianGe))
    dayCountList[jianGeDate.date] = jianGeDate

# 保存原来承办部门
indexChengBan = hanchxl.get_col_index(sheetJieDan, '承办部门')
sheetJieDan.insert_cols(indexChengBan, 1)
sheetJieDan.cell(1, indexChengBan).value = '承办部门-原'
indexChengBan = hanchxl.get_col_index(sheetJieDan, '承办部门')
maxRow = sheetJieDan.max_row
for row in range(2, maxRow+1):
    sheetJieDan.cell(row, indexChengBan-1).value = sheetJieDan.cell(row, indexChengBan).value

# 修改承办部门
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难工单', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '疑难案件', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '应急小分队', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '流动人口管理组', '社会治理组')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', '综合城管', '综合行政执法队')

# 市派工单 数量
countShiPai = 0
# 区派工单 数量
countQuPai = 0
# 分类统计（一级分类）
countFenLei = {}
# 承办部门-科室
chengBanKeShi = {}
# 承办部门-社区村
chengBanSheQu = {}
indexPaiDan = hanchxl.get_col_index(sheetJieDan, '派单时间')
indexLeixing = hanchxl.get_col_index(sheetJieDan, '工单类型(直派/转派)')
indexFenLei = hanchxl.get_col_index(sheetJieDan, '一级分类')
indexChengBan = hanchxl.get_col_index(sheetJieDan, '承办部门')
indexJieDan = hanchxl.get_col_index(sheetJieDan, '接单部门')
if indexPaiDan is not None and indexLeixing is not None:
    for rowSource in range(2, maxRowJieDan + 1):
        cellValuePaiDan = sheetJieDan.cell(rowSource, indexPaiDan).value
        cellValueLeiXing = sheetJieDan.cell(rowSource, indexLeixing).value
        cellValueFenLei = sheetJieDan.cell(rowSource, indexFenLei).value
        cellValueChengBan = sheetJieDan.cell(rowSource, indexChengBan).value
        cellValueJieDan = sheetJieDan.cell(rowSource, indexJieDan).value
        if cellValuePaiDan is not None and cellValueLeiXing is not None and cellValueFenLei is not None \
                and cellValueChengBan is not None and cellValueJieDan is not None:
            PaiDanStamp = int(time.mktime(cellValuePaiDan.timetuple()))
            for dayJianGe in dayCountList:
                if PaiDanStamp >= dayCountList[dayJianGe].start and PaiDanStamp < dayCountList[dayJianGe].end:
                    dayCountList[dayJianGe].count = dayCountList[dayJianGe].count + 1
                    # 判断 工单类型(直派/转派)
                    if cellValueLeiXing == '直派':
                        dayCountList[dayJianGe].shiPai = dayCountList[dayJianGe].shiPai + 1
                        countShiPai = countShiPai + 1
                    else:
                        dayCountList[dayJianGe].quPai = dayCountList[dayJianGe].quPai + 1
                        countQuPai = countQuPai + 1
                    # 判断工单分类
                    if cellValueFenLei in countFenLei:
                        countFenLei[cellValueFenLei] = countFenLei[cellValueFenLei] + 1
                    else:
                        countFenLei[cellValueFenLei] = 1
                    # 判断承办部门
                    if cellValueJieDan == '社区（村）':
                        if cellValueChengBan in chengBanSheQu:
                            chengBanSheQu[cellValueChengBan] = chengBanSheQu[cellValueChengBan] + 1
                        else:
                            chengBanSheQu[cellValueChengBan] = 1
                    else:
                        if cellValueChengBan in chengBanKeShi:
                            chengBanKeShi[cellValueChengBan] = chengBanKeShi[cellValueChengBan] + 1
                        else:
                            chengBanKeShi[cellValueChengBan] = 1
                    continue
listdelete = []
for dayJianGe in dayCountList:
    logger.debug('{} 有 {} 件工单'.format(dayCountList[dayJianGe].date, dayCountList[dayJianGe].count))
    if dayCountList[dayJianGe].count > 0:
        maxRowJieDan = sheetListOfJieDan['日诉求量'].max_row
        sheetListOfJieDan['日诉求量'].cell(maxRowJieDan + 1, 1).value = timeCurStr
        sheetListOfJieDan['日诉求量'].cell(maxRowJieDan + 1, 2).value = dayCountList[dayJianGe].date
        sheetListOfJieDan['日诉求量'].cell(maxRowJieDan + 1, 3).value = dayCountList[dayJianGe].count
        sheetListOfJieDan['日诉求量'].cell(maxRowJieDan + 1, 4).value = dayCountList[dayJianGe].quPai
        sheetListOfJieDan['日诉求量'].cell(maxRowJieDan + 1, 5).value = dayCountList[dayJianGe].shiPai
        logger.debug('{} {} {}'.format(dayCountList[dayJianGe].date, timeCurStr, dayCountList[dayJianGe].count))
    else:
        listdelete.append(dayJianGe)

# 获取周报数据 开始日期 和 结束日期
for index in listdelete:
    del dayCountList[index]

daysListKeys = list(dayCountList.keys())
daysStart = daysListKeys[0]
daysEnd = daysListKeys[len(daysListKeys)-1]
logger.debug('{} {} {}'.format(dayCountList[daysStart].date, timeCurStr, dayCountList[daysStart].count))
logger.debug('{} {} {}'.format(dayCountList[daysEnd].date, timeCurStr, dayCountList[daysEnd].count))
# 统计日诉求量 结束

# 保存 诉求情况 开始
countAll = countQuPai + countShiPai
strStartTime = '{}年{}月{}日'.format(dayCountList[daysStart].time_d.year, dayCountList[daysStart].time_d.month
                                     , dayCountList[daysStart].time_d.day)
strEndTime = '{}月{}日'.format(dayCountList[daysEnd].time_d.month, dayCountList[daysEnd].time_d.day)
maxRow = sheetListOfJieDan['总体诉求情况'].max_row
sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 1).value = timeCurStr
if maxRow >= 2:
    sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 2).value = sheetListOfJieDan['总体诉求情况'].cell(maxRow, 2).value + 1
else:
    sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 2).value = 1
sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 3).value = countAll
sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 4).value = countQuPai
sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 5).value = countShiPai
sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 6).value = strStartTime
sheetListOfJieDan['总体诉求情况'].cell(maxRow + 1, 7).value = strEndTime

countAllLast = countAll
if maxRow >= 2:
    countAllLast = sheetListOfJieDan['总体诉求情况'].cell(maxRow, 3).value
countChaZhi = countAll - countAllLast
zhiHuanBi = None
if countChaZhi > 0:
    zhiHuanBi = '上升{}%'.format(round(countChaZhi*100/countAllLast, 2))
else:
    zhiHuanBi = '下降{}%'.format(round(-countChaZhi * 100 / countAllLast, 2))

strPrint = '{}至{}，我街道市民诉求处置中心接收诉求{}件，' \
           '与上周（{}件）相比增加{}件，环比{}，' \
           '日均{}件，其中区派工单{}件，市派工单{}件。'.format(strStartTime, strEndTime, countAll, countAllLast, countChaZhi
                                                              , zhiHuanBi, round(countAll/7), countQuPai, countShiPai)

logger.debug('{}'.format(strPrint))
# 修改周报 诉求情况 数据
for para in docMuBan.paragraphs:
    for run in para.runs:
        if '{周开始日期}' in run.text:
            run.text = run.text.replace('{周开始日期}', strStartTime)
        if '{周结束日期}' in run.text:
            run.text = run.text.replace('{周结束日期}', strEndTime)
        if '{周接单量}' in run.text:
            run.text = run.text.replace('{周接单量}', '{}'.format(countAll))
        if '{上周接单量}' in run.text:
            run.text = run.text.replace('{上周接单量}', '{}'.format(countAllLast))
        if '{周接单增量}' in run.text:
            countChaZhi = countAll - countAllLast
            if countChaZhi > 0:
                run.text = run.text.replace('{周接单增量}', '增加{}'.format(countChaZhi))
                run.text = run.text.replace('{周接单增率}', '上升{}'.format(round(countChaZhi/countAllLast), 2))
            else:
                run.text = run.text.replace('{周接单增量}', '减少{}'.format(-countChaZhi))
                run.text = run.text.replace('{周接单增率}', '下降{}'.format(round(-countChaZhi/countAllLast), 2))
        if '{日均接单量}' in run.text:
            run.text = run.text.replace('{日均接单量}', '{}'.format(round(countAll/7)))
        if '{周市派工单量}' in run.text:
            run.text = run.text.replace('{周市派工单量}', '{}'.format(countShiPai))
        if '{周区派量}' in run.text:
            run.text = run.text.replace('{周区派量}', '{}'.format(countQuPai))
# 保存 诉求情况 结束

# 保存 诉求分类情况 一级分类 开始
countFenLei = dict(sorted(countFenLei.items(), key=lambda kv: kv[1], reverse=True))
maxRow = sheetListOfJieDan['分类统计'].max_row
sheetListOfJieDan['分类统计'].cell(maxRow + 1, 1).value = timeCurStr
sheetListOfJieDan['分类统计'].cell(maxRow + 1, 2).value = countAll
for index in countFenLei:
    logger.debug('{} 有 {} 件'.format(index, countFenLei[index]))
    maxCol = sheetListOfJieDan['分类统计'].max_column
    indexLeiXingSearch = hanchxl.get_col_index(sheetListOfJieDan['分类统计'], index)
    if indexLeiXingSearch is None:
        indexLeiXingSearch = maxCol + 1
        sheetListOfJieDan['分类统计'].cell(1, indexLeiXingSearch).value = index
    sheetListOfJieDan['分类统计'].cell(maxRow + 1, indexLeiXingSearch).value = countFenLei[index]

# 计算前十类问题 数量及占比
nNum = 1
nCount = 0
strFenLei = ''
for index in countFenLei:
    if nNum > 10:
        break
    nCount = nCount + countFenLei[index]
    if nNum == 1:
        strFenLei = index
    else:
        strFenLei = strFenLei + '、' + index
    logger.debug('{} 有 {} 件'.format(index, countFenLei[index]))
    nNum = nNum + 1
strQianShi = '本周诉求量前十的问题共{}件，'.format(nCount)
strQianShi = strQianShi + '，占诉求总量的{}%，'.format(round(nCount/countAll*100, 2))
strQianShi = strQianShi + '分别为{}。'.format(strFenLei)
logger.debug('{}'.format(strQianShi))
# 修改周报 诉求情况 数据
for para in docMuBan.paragraphs:
    for run in para.runs:
        # logger.debug('{}'.format(run.text))
        if '{前十分类诉求名称}。' in run.text:
            run.text = run.text.replace('{前十分类诉求名称}。', strFenLei + '。')
        if '{前十分类诉求量}' in run.text:
            run.text = run.text.replace('{前十分类诉求量}', '{}'.format(nCount))
        if '{前十分类诉求占比}' in run.text:
            run.text = run.text.replace('{前十分类诉求占比}', '{}'.format(round(nCount/countAll*100, 2)))
# 保存 诉求分类情况 一级分类 结束
# sheetNamesList = ['日诉求量', '分类统计', '科室接单量', '社区村接单量', '总体诉求情况']
# 保存  科室 和 社区村 的接单量 开始
chengBanKeShi = dict(sorted(chengBanKeShi.items(), key=lambda kv: kv[1], reverse=True))
logger.debug('{}'.format(chengBanKeShi))
maxRow = sheetListOfJieDan['科室接单量'].max_row
sheetListOfJieDan['科室接单量'].cell(maxRow + 1, 1).value = timeCurStr
countJieDanKeShi = 0
countJieDanKeShiFive = 0
for indexKeShi in chengBanKeShi:
    logger.debug('{} 有 {} 件'.format(indexKeShi, chengBanKeShi[indexKeShi]))
    maxCol = sheetListOfJieDan['科室接单量'].max_column
    indexJieDanSearch = hanchxl.get_col_index(sheetListOfJieDan['科室接单量'], indexKeShi)
    if indexJieDanSearch is None:
        indexJieDanSearch = maxCol + 1
        sheetListOfJieDan['科室接单量'].cell(1, indexJieDanSearch).value = indexKeShi
    sheetListOfJieDan['科室接单量'].cell(maxRow + 1, indexJieDanSearch).value = chengBanKeShi[indexKeShi]
    countJieDanKeShi = countJieDanKeShi + chengBanKeShi[indexKeShi]
    if list(chengBanKeShi.keys()).index(indexKeShi) < 5:
        countJieDanKeShiFive = countJieDanKeShiFive + chengBanKeShi[indexKeShi]
sheetListOfJieDan['科室接单量'].cell(maxRow + 1, 2).value = countJieDanKeShi

chengBanSheQu = dict(sorted(chengBanSheQu.items(), key=lambda kv: kv[1], reverse=True))
logger.debug('{}'.format(chengBanSheQu))
maxRow = sheetListOfJieDan['社区村接单量'].max_row
sheetListOfJieDan['社区村接单量'].cell(maxRow + 1, 1).value = timeCurStr
countJieDanSheQu = 0
countJieDanSheQuFive = 0
for index in chengBanSheQu:
    logger.debug('{} 有 {} 件'.format(index, chengBanSheQu[index]))
    maxCol = sheetListOfJieDan['社区村接单量'].max_column
    indexJieDanSearch = hanchxl.get_col_index(sheetListOfJieDan['社区村接单量'], index)
    if indexJieDanSearch is None:
        indexJieDanSearch = maxCol + 1
        sheetListOfJieDan['社区村接单量'].cell(1, indexJieDanSearch).value = index
    sheetListOfJieDan['社区村接单量'].cell(maxRow + 1, indexJieDanSearch).value = chengBanSheQu[index]
    countJieDanSheQu = countJieDanSheQu + chengBanSheQu[index]
    if list(chengBanSheQu.keys()).index(index) < 5:
        countJieDanSheQuFive = countJieDanSheQuFive + chengBanSheQu[index]
sheetListOfJieDan['社区村接单量'].cell(maxRow + 1, 2).value = countJieDanSheQu
# 保存  科室 和 社区村 的接单量 结束
# 计算 科室 和 社区村 的接单量 开始
for para in docMuBan.paragraphs:
    for run in para.runs:
        # logger.debug('{}'.format(run.text))
        if '{前五科室接单量}' in run.text:
            run.text = run.text.replace('{前五科室接单量}', '{}'.format(countJieDanKeShiFive))
        if '{前五科室接单占比}' in run.text:
            run.text = run.text.replace('{前五科室接单占比}', '{}'.format(round(countJieDanKeShiFive*100/countAll, 2)))
        if '{前五社区村接单量}' in run.text:
            run.text = run.text.replace('{前五社区村接单量}', '{}'.format(countJieDanSheQuFive))
        if '{前五社区村接单占比}' in run.text:
            run.text = run.text.replace('{前五社区村接单占比}', '{}'.format(round(countJieDanSheQuFive*100/countAll, 2)))

chengBanKeShiKeys = list(chengBanKeShi.keys())
chengBanSheQuKeys = list(chengBanSheQu.keys())
paiMingList = ['一', '二', '三', '四', '五']
for table in docMuBan.tables:
    for row in table.rows:
        for cell in row.cells:
            logger.debug('{}'.format(cell.text))
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    logger.debug('{}'.format(run.text))
                    for paiMingIndex in paiMingList:
                        # 替换接单前五科室信息
                        strChengBan = chengBanKeShiKeys[paiMingList.index(paiMingIndex)]
                        strReplace = '{科室接单第' + '{}'.format(paiMingIndex) + '名}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(strChengBan))
                        strReplace = '{件数科室' + '{}'.format(paiMingIndex) + '}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(chengBanKeShi[strChengBan]))
                        strReplace = '{占比科室' + '{}'.format(paiMingIndex) + '}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(
                                round(chengBanKeShi[strChengBan]*100/countJieDanKeShi, 2)))
                        colIndex = hanchxl.get_col_index(sheetListOfJieDan['科室接单量'], strChengBan)
                        maxRow = sheetListOfJieDan['科室接单量'].max_row
                        valueLast = 9999
                        if maxRow > 2:
                            valueLast = sheetListOfJieDan['科室接单量'].cell(maxRow-1, colIndex).value
                        strReplace = '{科室环比' + '{}'.format(paiMingIndex) + '}'
                        if strReplace in run.text:
                            valueZeng = chengBanKeShi[strChengBan] - valueLast
                            strValue = '-'
                            if valueZeng > 0 and valueLast != 9999:
                                strValue = '{}%↑'.format(round(valueZeng*100/valueLast))
                                run.font.color.rgb = RGBColor(222, 41, 16)
                            if valueZeng < 0 and valueLast != 9999:
                                strValue = '{}%↓'.format(round(-valueZeng * 100 / valueLast))
                                run.font.color.rgb = RGBColor(0, 139, 69)
                            if valueZeng == 0 and valueLast != 9999:
                                strValue = '0%'
                            run.text = run.text.replace(strReplace, strValue)
                        # 替换接单前五社区村信息
                        strChengBanSheQu = chengBanSheQuKeys[paiMingList.index(paiMingIndex)]
                        strReplace = '{社区村接单第' + '{}'.format(paiMingIndex) + '名}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(strChengBanSheQu))
                        strReplace = '{件数社区村' + '{}'.format(paiMingIndex) + '}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(chengBanSheQu[strChengBanSheQu]))
                        strReplace = '{占比社区村' + '{}'.format(paiMingIndex) + '}'
                        if strReplace in run.text:
                            run.text = run.text.replace(strReplace, '{}'.format(
                                round(chengBanSheQu[strChengBanSheQu] * 100 / countJieDanSheQu, 2)))
                        colIndex = hanchxl.get_col_index(sheetListOfJieDan['社区村接单量'], strChengBanSheQu)
                        maxRow = sheetListOfJieDan['社区村接单量'].max_row
                        valueLast = 9999
                        if maxRow > 2:
                            valueLast = sheetListOfJieDan['社区村接单量'].cell(maxRow-1, colIndex).value
                        strReplace = '{社区村环比' + '{}'.format(paiMingIndex) + '}'
                        if strReplace in run.text:
                            valueZeng = chengBanSheQu[strChengBanSheQu] - valueLast
                            strValue = '-'
                            if valueZeng > 0 and valueLast != 9999:
                                strValue = '{}%↑'.format(round(valueZeng*100/valueLast))
                                run.font.color.rgb = RGBColor(222, 41, 16)
                            if valueZeng < 0 and valueLast != 9999:
                                strValue = '{}%↓'.format(round(-valueZeng * 100 / valueLast))
                                run.font.color.rgb = RGBColor(0, 139, 69)
                            if valueZeng == 0 and valueLast != 9999:
                                strValue = '0%'
                            run.text = run.text.replace(strReplace, strValue)
                    if '{件数科室合计}' in run.text:
                        run.text = run.text.replace('{件数科室合计}', '{}'.format(countJieDanKeShiFive))
                    if '{占比科室合计}' in run.text:
                        run.text = run.text.replace('{占比科室合计}', '{}'.format(round(countJieDanKeShiFive*100/countJieDanKeShi, 2)))
                    if '{件数社区村合计}' in run.text:
                        run.text = run.text.replace('{件数社区村合计}', '{}'.format(countJieDanSheQuFive))
                    if '{占比社区村合计}' in run.text:
                        run.text = run.text.replace('{占比社区村合计}', '{}'.format(round(countJieDanSheQuFive*100/countJieDanSheQu, 2)))




# 计算 科室 和 社区村 的接单量 结束
bookJieDan.save(fileJieDanSave)
docMuBan.save(fileZhouBaoSave)
bookCountZhouBao.save(fileCountZhouBao)


